/*
 * このクラスの使用例
 *
 * 外部のプログラムで例のように呼び出す
 *
 * 例1
 * 	'Xxx'Bean bean=('Xxx'Bean)TableManager
 * 									.getRecord(TableManager.'XXX'S_TABLE,indexId)
 * 									.getBean();
 *
 * ※「'xxx'」の部分に担当のパッケージ名、indexId(int型)にプライマリーキーをいれると
 * 　 プライマリーキーに対応したレコードをbeanとして取り出せます
 *
 * 例2
 * 	'Xxx'Bean bean=('Xxx'Bean)TableManager
 * 									.getRecord(TableManager.'XXX'S_TABLE,sql)
 * 									.getBean();
 *
 * ※「'xxx'」の部分に担当のパッケージ名、sql(String型)にSQLの文字列をいれると
 * 　 SQLに対応したレコードを1つをbeanとして取り出せます
 *
 */

package common;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import manager.ManagerBean;
import member.MemberBean;
import book.BookBean;
import book.RentalBean;
import book.ReserveBean;


public class TableManager
{
	//Table指定用の定数
	public static final int MANAGERS_TABLE=0;
	public static final int MEMBERS_TABLE=1;
	public static final int BOOKS_TABLE=2;
	public static final int RENTALS_TABLE=3;
	public static final int RESERVES_TABLE=4;
	public static final int UPDATES_TABLE=5;

	private static Connection connection=null;
	private static Statement statement=null;
	private static ResultSet resultSet=null;

	//Table指定用の定数とプライマリーキーからレコードを抜き出す
	public static Bean<?> getRecord (int table,int indexId)
	{
		Bean<?> bean;

		connection=DBManager.getConnection();

		try
		{
			switch(table)
			{
				case MANAGERS_TABLE:
					bean=getManagerRecord(indexId);
					break;
				case MEMBERS_TABLE:
					bean=getMemberRecord(indexId);
					break;
				case BOOKS_TABLE:
					bean=getBookRecord(indexId);
					break;
				case RENTALS_TABLE:
					bean=getRentalRecord(indexId);
					break;
				case RESERVES_TABLE:
					bean=getReserveRecord(indexId);
					break;
				default:
					return null;
			}

			return(bean);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		catch(NumberFormatException e)
		{
			e.printStackTrace();
		}
		finally
		{
			if(statement!=null)
			{
				try
				{
					statement.close();
				}
				catch (SQLException e)
				{
					e.printStackTrace();
				}
			}

			if(connection!=null)
			{
				try
				{
					connection.close();
				}
				catch (SQLException e)
				{
					e.printStackTrace();
				}
			}
		}

		return null;
	}

	protected static Bean<ManagerBean>  getManagerRecord(int indexId) throws SQLException
	{
		ManagerBean managerBean=new ManagerBean();

		statement=connection.createStatement();
		resultSet=statement.executeQuery("SELECT * FROM managers WHERE ID = "+indexId);

		while(resultSet.next())
		{
			managerBean.setId(resultSet.getInt("ID"));
			managerBean.setName(resultSet.getString("NAME"));
			managerBean.setRuby(resultSet.getString("RUBY"));
			managerBean.setUpperLevel(resultSet.getShort("UPPER_LEVEL"));
			managerBean.setPassword(resultSet.getString("PASSWORD"));
			managerBean.setTime(resultSet.getString("TIME"));
			managerBean.setDeleteFlag(resultSet.getInt("DELETE_FLAG"));
		}

		Bean<ManagerBean> bean=new Bean<ManagerBean>(managerBean);

		return bean;
	}

	protected static Bean<MemberBean>  getMemberRecord(int indexId) throws SQLException
	{
		MemberBean memberBean=new MemberBean();

		statement=connection.createStatement();
		resultSet=statement.executeQuery("SELECT * FROM members WHERE ID = "+indexId);

		while(resultSet.next())
		{
			memberBean.setId(resultSet.getInt("ID"));
			memberBean.setName(resultSet.getString("NAME"));
			memberBean.setRuby(resultSet.getString("RUBY"));
			memberBean.setPassword(resultSet.getString("PASSWORD"));
			memberBean.setUserId(resultSet.getString("USER_ID"));
			//memberBean.setDeleteFlag(resultSet.getInt("DELETE_FLAG"));
		}

		Bean<MemberBean> bean=new Bean<MemberBean>(memberBean);

		return bean;
	}

	protected static Bean<BookBean>  getBookRecord(int indexId) throws SQLException
	{
		BookBean bookBean=new BookBean();

		statement=connection.createStatement();
		resultSet=statement.executeQuery("SELECT * FROM books WHERE ID = "+indexId);

		while(resultSet.next())
		{
			bookBean.setId(resultSet.getInt("ID"));
			bookBean.setTitle(resultSet.getString("TITLE"));
			bookBean.setWriter(resultSet.getString("WRITER"));
			bookBean.setPublisherId(resultSet.getInt("PUBLISHER_ID"));
			bookBean.setGenreId(resultSet.getInt("GENRE_ID"));
			bookBean.setPublicationDate(resultSet.getString("PUBLICATION_DATE"));
			bookBean.setIsbn(resultSet.getString("ISBN"));
			bookBean.setContent(resultSet.getString("CONTENT"));
			bookBean.setPicture(resultSet.getString("PICTURE"));
			bookBean.setDeleteFlag(resultSet.getInt("DELETE_FLAG"));
			bookBean.setOpenFlag(resultSet.getInt("OPEN_FLAG"));
			//bookBean.setRentalId(resultSet.getString("RENTAL_ID"));
			//bookBean.setNewestId(resultSet.getString("NEWEST_ID"));
		}

		Bean<BookBean> bean=new Bean<BookBean>(bookBean);

		return bean;
	}

	protected static Bean<RentalBean>  getRentalRecord(int indexId) throws SQLException
	{
		RentalBean rentalBean=new RentalBean();

		statement=connection.createStatement();
		resultSet=statement.executeQuery("SELECT * FROM rentals WHERE ID = "+indexId);

		while(resultSet.next())
		{
			rentalBean.setId(resultSet.getInt("ID"));
			rentalBean.setBookId(resultSet.getInt("BOOK_ID"));
			rentalBean.setMemberId(resultSet.getInt("MEMBER_ID"));
			rentalBean.setRentalDate(resultSet.getDate("RENTAL_DATE"));
			rentalBean.setReturnDate(resultSet.getDate("RETURN_DATE"));
			rentalBean.setReturnPlanDate(resultSet.getDate("RETURN_PLAN_DATE"));
			rentalBean.setReserveId(resultSet.getInt("RESERVE_ID"));
			rentalBean.setMailFlag(resultSet.getInt("MAIL_FLAG"));
		}

		Bean<RentalBean> bean=new Bean<RentalBean>(rentalBean);

		return bean;
	}

	protected static Bean<ReserveBean> getReserveRecord(int indexId) throws SQLException
	{
		ReserveBean reserveBean = new ReserveBean();

		statement=connection.createStatement();
		resultSet=statement.executeQuery("SELECT * FROM reserves WHERE ID = "+indexId);

		while(resultSet.next())
		{
			reserveBean.setId(resultSet.getInt("ID"));
			reserveBean.setReserveDate(resultSet.getDate("RESERVE_DATE"));
			reserveBean.setBookIsbn(resultSet.getString("BOOK_ISBN"));
			reserveBean.setMemberId(resultSet.getString("MEMBER_ID"));
			reserveBean.setMailFlag(resultSet.getInt("MAIL_FLAG"));
		}
			Bean<ReserveBean> bean = new Bean<ReserveBean>(reserveBean);

			return bean;
	}


	public static Bean<?> getRecord(int table,String sql)
	{
		Bean<?> bean;

		connection=DBManager.getConnection();

		try
		{
			switch(table)
			{
				case MANAGERS_TABLE:
					bean=getManagerRecord(sql);
					break;
				case MEMBERS_TABLE:
					bean=getMemberRecord(sql);
					break;
				case BOOKS_TABLE:
					bean=getBookRecord(sql);
					break;
				case RENTALS_TABLE:
					bean=getRentalRecord(sql);
					break;
				case RESERVES_TABLE:
					bean=getReserveRecord(sql);
					break;
				default:
					return null;
			}

			return(bean);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		catch(NumberFormatException e)
		{
			e.printStackTrace();
		}
		finally
		{
			if(statement!=null)
			{
				try
				{
					statement.close();
				}
				catch (SQLException e)
				{
					e.printStackTrace();
				}
			}

			if(connection!=null)
			{
				try
				{
					connection.close();
				}
				catch (SQLException e)
				{
					e.printStackTrace();
				}
			}
		}

		return null;
	}

	protected static Bean<ManagerBean>  getManagerRecord(String sql) throws SQLException
	{
		ManagerBean managerBean=new ManagerBean();

		statement=connection.createStatement();
		resultSet=statement.executeQuery(sql);

		while(resultSet.next())
		{
			managerBean.setId(resultSet.getInt("ID"));
			managerBean.setName(resultSet.getString("NAME"));
			managerBean.setRuby(resultSet.getString("RUBY"));
			managerBean.setUpperLevel(resultSet.getShort("UPPER_LEVEL"));
			managerBean.setPassword(resultSet.getString("PASSWORD"));
			managerBean.setTime(resultSet.getString("TIME"));
			managerBean.setDeleteFlag(resultSet.getInt("DELETE_FLAG"));
		}

		Bean<ManagerBean> bean=new Bean<ManagerBean>(managerBean);

		return bean;
	}

	protected static Bean<MemberBean>  getMemberRecord(String sql) throws SQLException
	{
		MemberBean memberBean=new MemberBean();

		statement=connection.createStatement();
		resultSet=statement.executeQuery(sql);

		while(resultSet.next())
		{
			memberBean.setId(resultSet.getInt("ID"));
			memberBean.setName(resultSet.getString("NAME"));
			memberBean.setRuby(resultSet.getString("RUBY"));
			memberBean.setPassword(resultSet.getString("PASSWORD"));
			memberBean.setUserId(resultSet.getString("USER_ID"));
			//memberBean.setDeleteFlag(resultSet.getInt("DELETE_FLAG"));
		}

		Bean<MemberBean> bean=new Bean<MemberBean>(memberBean);

		return bean;
	}

	protected static Bean<BookBean>  getBookRecord(String sql) throws SQLException
	{
		BookBean bookBean=new BookBean();

		statement=connection.createStatement();
		resultSet=statement.executeQuery(sql);

		while(resultSet.next())
		{
			bookBean.setId(resultSet.getInt("ID"));
			bookBean.setTitle(resultSet.getString("TITLE"));
			bookBean.setWriter(resultSet.getString("WRITER"));
			bookBean.setPublisherId(resultSet.getInt("PUBLISHER_ID"));
			bookBean.setGenreId(resultSet.getInt("GENRE_ID"));
			bookBean.setPublicationDate(resultSet.getString("PUBLICATION_DATE"));
			bookBean.setIsbn(resultSet.getString("ISBN"));
			bookBean.setContent(resultSet.getString("CONTENT"));
			bookBean.setPicture(resultSet.getString("PICTURE"));
			bookBean.setDeleteFlag(resultSet.getInt("DELETE_FLAG"));
			bookBean.setOpenFlag(resultSet.getInt("OPEN_FLAG"));
			//bookBean.setRentalId(resultSet.getString("RENTAL_ID"));
			//bookBean.setNewestId(resultSet.getString("NEWEST_ID"));
		}

		Bean<BookBean> bean=new Bean<BookBean>(bookBean);

		return bean;
	}

	protected static Bean<RentalBean>  getRentalRecord(String sql) throws SQLException
	{
		RentalBean rentalBean=new RentalBean();

		statement=connection.createStatement();
		resultSet=statement.executeQuery(sql);

		while(resultSet.next())
		{
			rentalBean.setId(resultSet.getInt("ID"));
			rentalBean.setBookId(resultSet.getInt("BOOK_ID"));
			rentalBean.setMemberId(resultSet.getInt("MEMBER_ID"));
			rentalBean.setRentalDate(resultSet.getDate("RENTAL_DATE"));
			rentalBean.setReturnDate(resultSet.getDate("RETURN_DATE"));
			rentalBean.setReturnPlanDate(resultSet.getDate("RETURN_PLAN_DATE"));
			rentalBean.setReserveId(resultSet.getInt("RESERVE_ID"));
		}

		Bean<RentalBean> bean=new Bean<RentalBean>(rentalBean);

		return bean;
	}

	protected static Bean<ReserveBean> getReserveRecord(String sql) throws SQLException
	{
		ReserveBean reserveBean = new ReserveBean();

		statement=connection.createStatement();
		resultSet=statement.executeQuery(sql);

		while(resultSet.next())
		{
			reserveBean.setId(resultSet.getInt("ID"));
			reserveBean.setReserveDate(resultSet.getDate("RESERVE_DATE"));
			reserveBean.setBookIsbn(resultSet.getString("BOOK_ISBN"));
			reserveBean.setMemberId(resultSet.getString("MEMBER_ID"));
			reserveBean.setMailFlag(resultSet.getInt("MAIL_FLAG"));
		}
			Bean<ReserveBean> bean = new Bean<ReserveBean>(reserveBean);

			return bean;
	}

	public static int changeMailFlag(int table,int indexId)
	{
		connection=DBManager.getConnection();

		String sql=null;
		int count=-1;

		try
		{
			statement=connection.createStatement();

			switch(table)
			{
				case RENTALS_TABLE:
					sql="UPDATE rentals SET MAIL_FLAG = 1 WHERE ID ="+indexId;
					break;
				case RESERVES_TABLE:
					sql="UPDATE reserves SET MAIL_FLAG = 1 WHERE ID ="+indexId;
					break;
				default:
					System.out.println("error");
					break;
			}
			System.out.println(sql);
			count=statement.executeUpdate(sql);

		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			if(statement!=null)
			{
				try
				{
					statement.close();
				}
				catch (SQLException e)
				{
					e.printStackTrace();
				}
			}

			if(connection!=null)
			{
				try
				{
					connection.close();
				}
				catch (SQLException e)
				{
					e.printStackTrace();
				}
			}
		}
		
		return(count);
	}
}
